Microsoft Word and Excel Reports

The following steps should only need to be performed once.

If the Developer tab does not display in Word / Excel:

  1. In Word / Excel, click File>Options>Customize Ribbon.
  2. With Main Tabs selected from the Customize the Ribbon drop list (1), select the Developer check box (2):

    Developer tab

In order to run the macros that we will create with our reports we need to ensure that the Security level in Word / Excel is appropriate:

  1. On the Developer tab, click Macro Security.
  2. If it is not already selected, click the radio button for Disable all macros with notification:

    Trust Center

    With this setting, macros are disabled, but security alerts appear if there are macros present. You enable macros on a case-by-case basis.

    Note: You could select Enable all macros in order to avoid the need to enable macros on a case-by-case basis however be aware that there is a security risk in doing so.

  3. Click OK to close the Trust Center.

When we run a report in EMu, one or more files are generated with the report data. These files hold the data that the third party reporting tools (Crystal Reports, Microsoft Word and Excel for instance) use to generate the report.

What kind of files are generated will depend on the Type of report you are creating:

  • EMu 4.3 and earlier: report generation typically makes use of ODBC (Open Database Connectivity) and most report Types will generate one or more .csv files.
  • EMu 5.0 onwards: it is also possible to create ADO reports that report directly to an ActiveX Data Objects (ADO) RecordSet object (creating a .xml file) rather than reporting to an ODBC data source (which creates one or more .csv files).

Note: More detail about what type of files are created and where they are located is available here.

Read on for details about:

When mail merge facilities were introduced to Microsoft Word, the source of the data for merging in the document was a simple text file. Users were required to edit a text file and add data, one entry per line for each mail merge record required. To run the mail merge, Word needed to know the exact location of the text file that contained the mail merge records. It stored this location in the master merge document so that it could regenerate the merged document when required.

With the introduction of ODBC by Microsoft, the data source is hidden behind a name (DSN) that programs such as Word and Excel can use without needing to know the location of the data source. When creating a report in MS Word or Excel in versions prior to Office 2013 it is important that the DSN is specified using Visual Basic code.

In versions of MS Word and Excel from Office 2013 onwards it is not necessary to specify the DSN using Visual Basic code and the DSN can be specified using a Wizard.

This table provides details of EMu DSN names and the name of the .csv files generated when a report is run out of EMu:

Module Name

DSN

.csv File

Link Column

Accession Lots

EMu Accession Lots

eaccessi.csv

eaccessionlots_key

Admin Tasks

EMu Admin Tasks

eadmin.csv

eadmin_key

Audit Trails

EMu Audit Trails

eaudit.csv

eaudit_key

Bibliography

EMu Bibliography

ebibliog.csv

ebibliography_key

Catalogue

EMu Catalogue

ecatalog.csv

ecatalogue_key

Collection Events

EMu Collection Events

ecollect.csv

ecollectionevents_key

Condition Checks

EMu Condition Checks

econditi.csv

econdition_key

Conservation

EMu Conservation

econserv.csv

econservation_key

Events

EMu Events

eevents.csv

eevents_key

Gazetteer

EMu Gazetteer

egazette.csv

egazetteer_key

Groups

EMu Groups

egroups.csv

egroups_key

Insurance

EMu Insurance

einsuran.csv

einsurance_key

Internal Movements

EMu Internal Movements

einterna.csv

einternal_key

Loans

EMu Loans

eloans.csv

eloans_key

Locations

EMu Locations

elocatio.csv

elocations_key

Lookup Lists

EMu Lookup Lists

eluts.csv

eluts_key

Movements

EMu Movements

emovemen.csv

emovements_key

Multimedia

EMu Multimedia

emultime.csv

emultimedia_key

Narratives

EMu Narratives

enarrati.csv

enarratives_key

Parties

EMu Parties

eparties.csv

eparties_key

Propagation

EMu Propagation

epropaga.csv

epropagation_key

Registry

EMu Registry

eregistr.csv

eregistry_key

Rights

EMu Rights

erights.csv

erights_key

Sites

EMu Sites

esites.csv

esites_key

Statistics

EMu Statistics

estatist.csv

estatistics_key

Task Templates

EMu Task Templates

etemplat.csv

etemplate_key

Taxonomy

EMu Taxonomy

etaxonom.csv

etaxonomy_key

Thesaurus

EMu Thesaurus

ethesaur.csv

ethesaurus_key

Transactions

EMu Transactions

etransac.csv

etransactions_key

Valuations

EMu Valuations

evaluati.csv

evaluations_key

Voucher Payments

EMu Voucher Payments

evoucher.csv

evoucher_key

DSNs are stored (and can be modified) in the ODBC Data Source Administrator, which can be accessed by selecting:

  • Windows 7: Start>All Programs>Administrative Tools>Data Sources (ODBC)
  • Windows 8: Control Panel>System and Security>Administrative Tools>ODBC Data Sources

  • Windows 8/10: Control Panel>System and Security>Administrative Tools>ODBC Data Sources (32-bit)

  • Windows 8.1: Control Panel >Administrative Tools>ODBC Data Sources (32-bit)

To connect to an ADO RecordSet when building a Microsoft ADO Report it is necessary to ensure that Word and Excel are configured to use the Microsoft ActiveX Data Objects Library, and to specify the link to the ADO RecordSet:

  1. On the Developer tab in Excel, click Visual Basic.

    Microsoft Visual Basic for Applications will open.

    Tip: If the Developer tab does not display, ensure that Excel has been set up correctly.

  2. Select Tools>References from the Menu bar.

    The References - VBAProject box displays.

  3. If the checkbox for Microsoft ActiveX Data Objects 6.1 Library is not ticked, click the checkbox:

  4. Click OK.

When writing the Visual Basic code for your report it is necessary to specify the location of the xmldata file generated by EMu.

The location of this file can vary, but typically it can be found in:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]

For example, a report run in the Parties module, will save the xmldata file to:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties

Note: More details here.

Things to note:

  • The xmldata file is always called xmldata.xml.
  • xmldata.xml is located in a folder named after the module in which the report was run. In the following example, the report was run in the Parties module and xmldata.xml is located in a folder called eparties:

The following needs to be added to your VB code (be sure to specify the correct module folder):

'Get the persisted record set
source = Environ("LocalAppData") & "\KESoftware\Reports\eparties\xmldata.xml"
Set RecordSet = New ADODB.RecordSet
RecordSet.Open source, "Provider=MSPersist"

Tip: Example code for an Excel ADO Report is provided here.